const db = require('../config/db');

const OrderModel = {
  // 创建订单
  createOrder: async (orderData) => {
    const {
      user_id,
      total_amount,
      status = 'pending',
      address,
      phone,
      note,
      items
    } = orderData;
    
    // 创建订单主表
    const [orderResult] = await db.execute(
      'INSERT INTO orders (user_id, total_amount, status, address, phone, note) VALUES (?, ?, ?, ?, ?, ?)',
      [user_id, total_amount, status, address, phone, note]
    );
    
    const orderId = orderResult.insertId;
    
    // 创建订单详情
    for (const item of items) {
      await db.execute(
        'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
        [orderId, item.product_id, item.quantity, item.price]
      );
    }
    
    return orderId;
  },
  
  // 获取用户订单列表
  getOrdersByUserId: async (userId, options = {}) => {
    let query = `
      SELECT o.*, 
             (SELECT SUM(quantity * price) FROM order_items WHERE order_id = o.id) as total_amount,
             o.status, o.address, o.phone, o.note, o.created_at
      FROM orders o
      WHERE o.user_id = ?
    `;
    
    const params = [userId];
    
    if (options.status) {
      query += ' AND o.status = ?';
      params.push(options.status);
    }
    
    query += ' ORDER BY o.created_at DESC';
    
    if (options.limit) {
      query += ' LIMIT ?';
      params.push(options.limit);
    }
    
    const [rows] = await db.execute(query, params);
    return rows;
  },
  
  // 获取订单详情
  getOrderById: async (id) => {
    const [rows] = await db.execute(`
      SELECT o.*, 
             (SELECT SUM(quantity * price) FROM order_items WHERE order_id = o.id) as total_amount,
             o.status, o.address, o.phone, o.note, o.created_at
      FROM orders o
      WHERE o.id = ?
    `, [id]);
    
    if (rows.length === 0) return null;
    
    const order = rows[0];
    
    // 获取订单商品
    const [items] = await db.execute(`
      SELECT oi.*, p.name as product_name, p.image
      FROM order_items oi
      LEFT JOIN products p ON oi.product_id = p.id
      WHERE oi.order_id = ?
    `, [id]);
    
    order.items = items;
    return order;
  },
  
  // 更新订单状态
  updateOrderStatus: async (id, status) => {
    const [result] = await db.execute(
      'UPDATE orders SET status = ? WHERE id = ?',
      [status, id]
    );
    return result.affectedRows > 0;
  },
  
  // 取消订单
  cancelOrder: async (id) => {
    const [result] = await db.execute(
      'UPDATE orders SET status = "cancelled" WHERE id = ?',
      [id]
    );
    return result.affectedRows > 0;
  }
};

module.exports = OrderModel;